﻿using System;
//Description:零组件树形展示
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:Wangjh
//create Date:2020-4-16
//Rewriter:
//Rewrite Date:
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.DBUtility;

namespace uMES.LeanManufacturing.ReportBusiness
{
  public  class uMESProductTreeViewBusiness
    {
        /// <summary>
        /// 获取组件或整件所需零组件信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>

        public DataTable GetSubProductInfo2(Dictionary<string,string> para) {
            string strSql = @"select pp.productname,pp.productrev,'' productid,'' productStatus,pp.subproductname,pp.subproductrev,'' subproductid,'' subproductstatus  from productsubproduct pp";
            strSql += string.Format(" START WITH pp.productname='{0}' and pp.productrev='{1}'",para["ProductName"],para["ProductRev"]);
            strSql += @"CONNECT BY PRIOR pp.subproductname=pp.productname 
order by pp.productname,pp.subproductname";

            DataTable productDt= OracleHelper.Query(strSql).Tables[0];

            //处理每个零件完成情况
            DataTable productStatusDt = new DataTable();
            productStatusDt.Columns.AddRange(new[] { new DataColumn("ProductName"), new DataColumn("ProductRev") , new DataColumn("ProductID") , new DataColumn("ProductStatus") });

            //工作令号
            string processNo = para["ProcessNo"];
            for (int i= 0;i< productDt.Rows.Count;i++) {
                string productName = "", productRev = "";
                Tuple<string, string, int, int> productStatusInfo = new Tuple<string, string, int, int>("","",0,0);

                //处理父零件
                productName = productDt.Rows[i]["productname"].ToString();
                productRev = productDt.Rows[i]["productrev"].ToString();

                DataRow[] drs = productStatusDt.Select($"productname='{productName}' and productrev='{productRev}'");//判断是否已经处理过

                if (drs.Length == 0)
                {
                    productStatusInfo = GetFinshStatusByProduct(productName, productRev, processNo);

                    DataRow productStatusRow = productStatusDt.NewRow();
                    productStatusRow["ProductName"] = productName;
                    productStatusRow["ProductRev"] = productRev;
                    productStatusRow["ProductID"] = productStatusInfo.Item1;
                    productStatusRow["ProductStatus"] = productStatusInfo.Item2;

                    productDt.Rows[i]["ProductID"] = productStatusInfo.Item1;
                    productDt.Rows[i]["ProductStatus"] = productStatusInfo.Item2;
                }
                else {
                    productDt.Rows[i]["ProductID"] = drs[0]["ProductID"];
                    productDt.Rows[i]["productStatus"] = drs[0]["productStatus"];

                }
                //处理子零件
                productName = productDt.Rows[i]["subproductname"].ToString();
                productRev = productDt.Rows[i]["subproductrev"].ToString();

                drs = productStatusDt.Select($"productname='{productName}' and productrev='{productRev}'");//判断是否已经处理过

                if (drs.Length == 0)
                {
                    productStatusInfo = GetFinshStatusByProduct(productName, productRev, processNo);

                    DataRow productStatusRow = productStatusDt.NewRow();
                    productStatusRow["ProductName"] = productName;
                    productStatusRow["ProductRev"] = productRev;
                    productStatusRow["ProductID"] = productStatusInfo.Item1;
                    productStatusRow["ProductStatus"] = productStatusInfo.Item2;

                    productDt.Rows[i]["subproductid"] = productStatusInfo.Item1;
                    productDt.Rows[i]["subproductstatus"] = productStatusInfo.Item2;
                }
                else
                {
                    productDt.Rows[i]["subproductid"] = drs[0]["ProductID"];
                    productDt.Rows[i]["subproductstatus"] = drs[0]["productStatus"];

                }

            }

            return productDt;
        }
        /// <summary>
        /// 返回产品的id,状态，订单数，完工数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="revision"></param>
        /// <returns></returns>
        public Tuple<string,string,int,int> GetFinshStatusByProduct(string name,string revision,string processNo="") {
            string productName = "", productRev = "", productID = "", productStatus = "";
            int mfgQty = 0, finshedQty = 0;

            //处理父零件
            productName = name;
            productRev = revision;

            string productSql = @"select p.productid,sum(nvl(m.qty,0))mfgqty from product p
left join productbase pb on pb.productbaseid=p.productbaseid
left join mfgorder m on m.productid=p.productid ";
            if (!string.IsNullOrWhiteSpace(processNo))
            {
                productSql += $" and m.processno='{processNo}' ";
            }
            string sql = "";
            sql = productSql;
            sql += $" where  pb.productname='{productName}' and p.productrevision='{productRev}'";
            
            sql += " group by p.productid ";
                DataTable tempDt = OracleHelper.Query(sql).Tables[0];
                if (tempDt.Rows.Count > 0)
                {
                    productID = tempDt.Rows[0]["ProductID"].ToString();
                
                    mfgQty = Convert.ToInt32(tempDt.Rows[0]["mfgqty"]);//订单数量
                    if (mfgQty == 0)//无订单
                    {
                        productStatus = "未完成";
                    }
                    else
                    {
                    //查询零件所有批次完工数量
                    if (!string.IsNullOrWhiteSpace(processNo))//有工作令号
                    {
                        sql = @"select nvl(sum(c.qty),0) sumQty from container c 
left join mfgorder m on m.mfgorderid=c.mfgorderid
where c.finishstate=1";
                        sql +=string.Format(" and c.productid='{0}' and m.processno='{1}' ",productID,processNo);
                    }
                    else {
                        sql = $"select nvl(sum(c.qty),0) sumQty from container c where c.finishstate=1 and c.productid='{productID}'";
                    }
                    DataTable tempDt2 = OracleHelper.Query(sql).Tables[0];
                    if (tempDt2.Rows.Count > 0)
                    {
                        finshedQty = Convert.ToInt32(tempDt2.Rows[0]["sumQty"]);
                    }
                    if (finshedQty >= mfgQty)
                    {
                        productStatus = "已完成";
                    }
                    else {
                        productStatus = "未完成";
                    }
                    }


                }
            return new Tuple<string, string, int, int>(productID,productStatus,mfgQty,finshedQty);
        }

        /// <summary>
        /// 获取组件或整件所需零组件信息,只显示默认版本的子零组件
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>

        public DataTable GetSubProductInfo(Dictionary<string, string> para)
        {
            string strSql = @"select pp.productname,pp.productrev,'' productid,'' productStatus,pp.subproductname,pp.subproductrev,'' subproductid,'' subproductstatus  from productsubproduct pp";
            strSql += string.Format(" START WITH pp.productname='{0}' and pp.productrev='{1}'", para["ProductName"], para["ProductRev"]);
            strSql += @" CONNECT BY PRIOR pp.subproductname=pp.productname 
order by pp.productname,pp.subproductname";

            DataTable productDt = OracleHelper.Query(strSql).Tables[0];
            DataTable result = productDt.Clone();
            result.Columns.Add("IsCommon");//是否公共件
            string productSql = @"select p.productid,pb.revofrcdid,p2.productrevision revofrcdRev from product p
left join productbase pb on pb.productbaseid=p.productbaseid
left join product p2 on p2.productid=pb.revofrcdid
where pb.productname='{0}' and p.productrevision='{1}'";
            foreach (DataRow row in productDt.Rows) {               
                if (result.Select(string.Format("productname='{0}' and subproductname='{1}' ",row["productname"].ToString(),row["subproductname"].ToString())).Length > 0) //已存在不显示
                {
                    continue;
                }
                //父零件
                var temp= OracleHelper.Query(string.Format(productSql, row["productname"].ToString(), row["productrev"].ToString())).Tables[0];
                if (temp.Rows.Count == 0)
                    continue;
                if (row["productname"].ToString() != para["ProductName"] )//不是第一级
                {
                    row["productrev"] = temp.Rows[0]["revofrcdRev"].ToString();
                }
                string productId = "";
                productId = temp.Rows[0]["productid"].ToString();
                row["productid"] = productId;

                //子零件
                 temp = OracleHelper.Query(string.Format(productSql, row["subproductname"].ToString(), row["subproductrev"].ToString())).Tables[0];
                if (temp.Rows.Count == 0)
                    continue;
                productId = "";
                productId = temp.Rows[0]["productid"].ToString();
                row["subproductid"] = productId;
                row["subproductrev"]= temp.Rows[0]["revofrcdRev"].ToString();

                result.ImportRow(row);

                //公共件标记
                var tempDrs = result.Select($"subproductname='{row["subproductname"].ToString()}'");
                if (tempDrs.Length > 1)
                {
                    foreach (DataRow tempDr in tempDrs)
                    {
                        tempDr["IsCommon"] = 1;
                    }

                }

            }

            return result;
        }
     }
}
